#include "DataBase.h"

CDataBase::CDataBase()
{

}

CDataBase::~CDataBase()
{

}

//static member
CDataBase * CDataBase::DataBase = NULL;

CDataBase * CDataBase::GetInstance()
{
    if(DataBase == NULL)
    {
        DataBase = new CDataBase();
    }
    return DataBase;
}

bool CDataBase::Init()
{
    if( OpenDB("Data.db") == false)
        return false;

    CreateTables();

    if( InitDataBase(DEVICE_FILE) == false)
        return false;

    //CloseDB();

    return true;

}

bool CDataBase::OpenDB(const char *fileName)
{
    int rc;
    pDatabase = NULL;

    rc = sqlite3_open(fileName, &pDatabase);

    if(rc != SQLITE_OK)
    {
        cerr << "Open database failed!" << endl;
        return false;
        sqlite3_close(pDatabase);
    }
    else if(DEBUG == true)
        cout << "Open database successfully!" << endl;

    return true;
}

void CDataBase::CreateTables()
{
    int status;

    char strCreateDeviceTable[256] = "CREATE TABLE Device(\
            ZigbeeID VARCHAR(4) PRIMARY KEY,\
            RoomNum INTEGER,\
            DevType INTEGER\
            );";

    char strCreateTemAndHumDataTable[256] = "CREATE TABLE TempretureAndHumdityData(\
            DeviceAddress VARCHAR(4),\
            Date VARCHAR(16),\
            Time VARCHAR(16),\
            Humidity VARCHAR(4),\
            Tempreture VARCHAR(4)\
            );";

    char * ErrMessage;
    status = sqlite3_exec(pDatabase, strCreateDeviceTable, 0, 0, &ErrMessage);
    if(status != 0 && status != 1)
        cerr << "Err_Message: " << ErrMessage << endl;
    else if(DEBUG == true)
        cout << "Create data device table successfully!" << endl;

    status = sqlite3_exec(pDatabase, strCreateTemAndHumDataTable, 0, 0, &ErrMessage);
    if(status != 0 && status != 1)
        cerr << "Err_Message: " << ErrMessage << endl;
    else if(DEBUG == true)
        cout << "Create data tem and hum data table successfully!" << endl;

}

//函数名：
//函数功能:
//函数参数：
//返回值:
bool CDataBase::InitDataBase(const char *fileName)
{
    FILE *fp;
    if( (fp = fopen(fileName, "r")) == NULL)
    {
        cerr << "can't open the file!" << endl;
        return false;
    }

    char strLine[LINE_SIZE];
    fgets(strLine, LINE_SIZE, fp);
    fgets(strLine, LINE_SIZE, fp);

    int roomNum, devType;
    char address[5];
    int status;
    char * ErrMessage;

    sqlite3_exec(pDatabase, "BEGIN TRANSACTION;", 0, 0, 0);
    char sql_sentence[LINE_SIZE];
    while( fgets(strLine, LINE_SIZE, fp) != NULL )
    {

        if(strLine[0] != '#')
        {
            sscanf(strLine, "%s %x %x", address, &roomNum, &devType);
            sprintf(sql_sentence,"INSERT INTO Device VALUES('%s', '%d', '%d');",
                    address, roomNum, devType);
            status = sqlite3_exec(pDatabase, sql_sentence, 0, 0, &ErrMessage);
            if(status != 0)
                cout << "Message: " << ErrMessage << endl;

            else if(DEBUG == true)
                cout << "Init successfully!" << endl;
        }

    }
    sqlite3_exec(pDatabase, "COMMIT TRANSACTION;", 0, 0, 0);
    return true;

}

//函数名：
//函数功能:
//函数参数：
//返回值:
bool CDataBase::UpdateDeviceInfo(const char * fileName)
{
    //if( OpenDB("Data.db") == false)
    //    return false;

    int status;
    char strDropTable[64] = "DROP TABLE Device;";
    status = sqlite3_exec(pDatabase, strDropTable, 0, 0, 0);
    if(status != 0)
        cerr << "Drop Database error!" << endl;
    else if(DEBUG == true)
        cout << "Drop successfully!" << endl;
    InitDataBase(fileName);

    //CloseDB();
    return true;
}

//函数名：
//函数功能:
//函数参数：
//返回值:
bool CDataBase::InsertCachedData(const vector<CachedData> & vecCachedData)
{
    //if( OpenDB("Data.db") == false)
    //    return false;

    //cout << "CachedDatavalue" << CachedData << endl;
    char sql_sentence[LINE_SIZE];
    char time[16], date[16];
    CTool * tool = CTool::GetInstance();
    tool->GetRecentTime(time);
    tool->GetRecentDate(date);

    sqlite3_exec(pDatabase, "BEGIN TRANSACTION;", 0, 0, 0);
    for(int count = 0; count != vecCachedData.size(); count++)
    {
        if(vecCachedData[count].Type <= 0xF)
        {
            sprintf(sql_sentence,  "INSERT INTO TempretureAndHumdityData VALUES('%s', '%s', '%s', '%f', '%f')",
                   vecCachedData[count].Id, date, time, vecCachedData[count].Values.FirstValue,
                   vecCachedData[count].Values.SecondValue);
            sqlite3_exec(pDatabase, sql_sentence, 0, 0, 0);
        }
    }
    sqlite3_exec(pDatabase, "COMMIT TRANSACTION;", 0, 0, 0);

    //CloseDB();
    return true;
}

//函数名：
//函数功能:
//函数参数：
//返回值:
//此函数待修改
bool CDataBase::GetDataFromTheRoom(const int roomNum, vector<CachedData> &retCachedData)
{
    //if( OpenDB("Data.db") == false)
     //   return false;

    char **deviceQueryResult, **dataQueryResult;
    int deviceResultColumn = 0, deviceResultRow = 0;
    int dataResultRow = 0, dataResultColumn = 0;
    int type;

    char sqlSentence[256];
    sprintf(sqlSentence, "SELECT Address,DevType FROM %d;", roomNum);
    sqlite3_get_table(pDatabase, sqlSentence, &deviceQueryResult, &deviceResultRow, &deviceResultColumn, 0);
    if( deviceResultRow == 0)
        return false;

    for(int deviceRowCount = 1; deviceRowCount < deviceResultRow; deviceRowCount += 2)
    {
        sscanf(deviceQueryResult[deviceRowCount+1], "%d", &type);
        if(type <= 0xF )
        {    //为温湿度传感器
            sprintf(sqlSentence, "SELECT * FROM TempretureAndHumdityData WHERE DeviceAddress = %s;",
                    deviceQueryResult[deviceRowCount]);
            sqlite3_get_table(pDatabase, sqlSentence, &dataQueryResult, &dataResultRow, &dataResultColumn, 0);
            for(int dataRowCount = 1; dataRowCount < dataResultRow; dataRowCount += 5 )
            {
                CachedData newCachedata;
                sscanf(dataQueryResult[dataRowCount], "%s", newCachedata.Id);
                newCachedata.Type = type;
          //    待修改  sscanf(dataQueryResult[dataRowCount+3, "%s", newCachedata.Values.FirstValue);
            }
        }
    }

    //CloseDB();
    return true;
}


void CDataBase::Cleanup()
{
    CloseDB();

    if(DataBase)
    {
        delete DataBase;
    }
}

bool CDataBase::DeleteData90DaysAgo()
{
    //if( OpenDB("Data.db") == false)
     //   return false;

    char *date90DaysAgo;
    CTool * tool = CTool::GetInstance();
    tool->GetDaysAgo(date90DaysAgo, 90);

    char sql_sentence[128];
    int ret;

    //注意，这里需要遍历所有有关数据的表删除数据，所有的表名称应该存在一个全局变量或者静态变量中，在程序刚运行时初始化。
    sprintf(sql_sentence, "DELETE FROM TempretureAndHumdityData WHERE DATE = '%s' ;", date90DaysAgo);

    ret = sqlite3_exec(pDatabase, sql_sentence, 0, 0, 0);
    if(ret != 0)
        printf("Delete failed!\n");
    else if(DEBUG == true)
        printf("Delete successfully!\n");

   // CloseDB();
    return true;
}


void CDataBase::CloseDB()
{
    sqlite3_close(pDatabase);
}
















